import openpyxl
import time
import re
import pymysql
# 忽略警告
import warnings
# from win32com.client import Dispatch


class SocialSecurity:
    # 文件行处理
    change_row = 0
    # 文件列处理
    change_colunm = 0
    # 文件选择区域
    file_scope = ''
    # 文件起始列
    first_colum = ''
    # 文件尾列
    end_colum = ''
    # sql模板
    sql = ''
    # 改数据
    sql_up = ''
    # 回盘判定值
    judge = 0

    # 添加初始值
    def __init__(self):
        pass

    # 新增成功excel处理
    def add_success(self):
        self.change_row = 3
        self.file_scope = 'A5:Q'
        self.first_colum = 'A'
        self.end_colum = 'Q'
        self.sql = 'insert into psn_add_success(company_name,id_number,name,nation,first_date,monthly_salary,' \
                   'type_work,position,registered_residence_type,phone,culture,pension_time,unemployment_time,' \
                   'treat_time,hurt_time,birth_time,notes,status,update_time) values('
        self.sql_up = 'update psn_add_success set status=\'1\' where company_name =\'{}\''

    # 新增错误excel处理
    def add_error(self):
        self.change_row = 0
        self.file_scope = 'A4:R'
        self.first_colum = 'A'
        self.end_colum = 'R'
        self.sql = 'insert into psn_add_error(company_name,id_number,name,nation,first_date,monthly_salary,' \
                   'type_work,position,registered_residence_type,phone,culture,pension_time,unemployment_time,' \
                   'treat_time,hurt_time,birth_time,notes,add_error_reason,status,update_time) values('
        self.sql_up = 'update psn_add_error set status=\'1\' where company_name =\'{}\''

    # 减少成功excel处理
    def del_success(self):
        self.change_row = 3
        self.file_scope = 'A5:K'
        self.first_colum = 'A'
        self.end_colum = 'K'
        self.sql = 'insert into psn_del_success(company_name,personal_number,id_number,name,delete_time,' \
                   'delete_reason,pension_status,unemployment_status,treat_status,hurt_status,birth_status,' \
                   'status,update_time) values('
        self.sql_up = 'update psn_del_success set status=\'1\' where company_name =\'{}\''

    # 减少错误excel处理
    def del_error(self):
        self.change_row = 0
        self.file_scope = 'A4:L'
        self.first_colum = 'A'
        self.end_colum = 'K'
        self.sql = 'insert into psn_del_success(company_name,personal_number,id_number,name,delete_time,' \
                   'delete_reason,pension_status,unemployment_status,treat_status,hurt_status,birth_status,' \
                   'delete_error_reason,status,update_time) values('
        self.sql_up = 'update psn_del_error set status=\'1\' where company_name =\'{}\''

    # 回盘excel处理
    def unit_back(self):
        # 回盘判定值
        self.judge = 1
        self.change_row = 0
        self.file_scope = 'A3:AA'
        self.first_colum = 'A'
        self.end_colum = 'AA'
        self.sql = 'insert into unitback(company_name,personal_number,id_number,name,type_paper,' \
                   'sex,nation,registered_residence_type,date_birth,first_date,pension_status,' \
                   'pension_base,pension_time,unemployment_status,unemployment_base,unemployment_time,' \
                   'treat_status,treat_base,treat_time,hurt_status,hurt_base,hurt_time,brith_status,' \
                   'brith_base,birth_time,monthly_salary,identity,notes,status,update_time) values('
        self.sql_up = 'update unitback set status=\'1\' where company_name =\'{}\''


def sql_add(sql_text):
    conn = pymysql.connect(host="127.0.0.1", user="root", database="social_security", password="753951", charset="utf8")
    cursor = conn.cursor()
    cursor.execute(sql_text)
    conn.commit()
    cursor.close()
    conn.close()

def sql_up(sql_text):
    conn = pymysql.connect(host="127.0.0.1", user="root", database="social_security", password="753951", charset="utf8")
    cursor = conn.cursor()
    cursor.execute(sql_text)
    conn.commit()
    cursor.close()
    conn.close()

def data_return(company_name, file_path, task_value):
    warnings.filterwarnings('ignore')
    start_time = time.perf_counter()
    fixed_value = ['已通过信息-职工增加', '已通过信息-职工减少', '已通过信息-回盘打印', '未通过信息-职工增加', '未通过信息-职工减少']
    update_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
    # excel文件路径
    # file_name = 'psnAddSuccess股份202210.xlsx'
    # file_path = 'psnAddSuccess股份202210.xlsx'

    # excel组装

    workbook = openpyxl.load_workbook(file_path)

    # 获取单个表格数据
    # print(workbook.sheetnames)
    # print(workbook.sheetnames[0])
    page = workbook.sheetnames
    for i in range(len(page)):
        sheet = workbook[workbook.sheetnames[i]]
        # print(sheet1['F2'].value)
        # 获取单个表格坐标
        # print(sheet1['F2'].coordinate)
        # 获取活动表的行列数
        social_security = SocialSecurity()
        # 调用新增成功
        # social_security.add_success()

        # 调用新增错误
        # social_security.add_error()

        # 调用减少成功
        # social_security.del_success()

        # 接口
        if task_value == fixed_value[0]:
            social_security.add_success()
        elif task_value == fixed_value[1]:
            social_security.del_success()
        elif task_value == fixed_value[2]:
            social_security.unit_back()
        elif task_value == fixed_value[3]:
            social_security.add_error()
        elif task_value == fixed_value[4]:
            social_security.del_error()

        else:
            ts = time.strftime("%Y-%m-%d %H:%M", time.localtime())
            f = open('conf/social_security/synchronize/log.txt', 'a+')
            f.write('文件未识别：' + '\t' + file_path + '\t' + ts + '\n')
            f.close()
            print('未识别')
            return False

        # 修改数据
        sql_up(social_security.sql_up.format(company_name))
        rows = sheet.max_row - social_security.change_row

        # print('获取行数:',rows)
        # print('获取列数:',column)
        # 选择表区域数据
        # 新增成功表
        cell = sheet[social_security.file_scope + str(rows)]
        # sql组装参数
        add = social_security.sql
        for n in cell:
            for j in n:
                # SQL语句的第一次组装需要多添加一个逗号  限制匹配AA
                if re.match(social_security.first_colum, str(j.coordinate)) \
                        and not re.match('AA', str(j.coordinate)):
                    # 第一例添加公司名称
                    add = add + '\'' + company_name + '\''

                    # 回盘第一列额外添加
                    if social_security.judge == 1:
                        add = add + ',\'' + str(j.value) + '\''
                else:
                    if not j.value:
                        j.value = ''
                    # noinspection PyBroadException
                    # 去掉多余时间长度
                    # try:
                    #     # 将时间处理为年月日
                    #     sstr = str(j.value)
                    #     stime = sstr.split('00:00:00')[0].strip() + sstr.split('00:00:00')[1]
                    # # 列表索引超出范围
                    # except IndexError:
                    #     stime = str(j.value)
                    add = add + ',' + '\'' + str(j.value) + '\''
                if re.match(social_security.end_colum, str(j.coordinate)):
                    sqlinset = add + ',\'0\',' + '\'' + update_time + '\'' + ');'
                    # 初始化sql模板
                    if social_security.judge == 1:
                        # 回盘数据去.0数据
                        del_data = sqlinset.split('.0')
                        del_accept = ''
                        for d in range(len(del_data)):

                            del_accept = del_accept + del_data[d]
                        sqlinset = del_accept

                    # sql运行
                    sql_add(sqlinset)
                    add = social_security.sql

    end_time = time.perf_counter()
    print('r: %s' % (end_time - start_time))
    return {"information": "success"}
